package com.kaishengit.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import com.kaishengit.entity.Employee;
import com.kaishengit.entity.Idea;
import com.kaishengit.entity.Ideacomment;
import com.kaishengit.entity.Project;
import com.kaishengit.util.DBHelp;
import com.kaishengit.util.Page;
import com.kaishengit.util.RowMapper;

public class IdeaDao {
	private DBHelp<Idea> db = new DBHelp<Idea>();
	public Page<Idea> findListById(String projectid,Page<Idea> page) {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT employeeid,projectid,te.username,te.password,te.pic,te.email,");
		sql.append("tp.name,tp.desc,ti.id,ti.title,ti.content,ti.createtime ");
		sql.append("FROM t_employee AS te LEFT JOIN t_idea AS ti ");
		sql.append("ON ti.employeeid = te.id LEFT JOIN t_project AS tp ");
		sql.append("ON ti.projectid = tp.id ");
		sql.append("WHERE projectid = ? ORDER BY createtime DESC limit ?,? ");
		
		List<Idea> list = db.executeQueryForList(sql.toString(), new IdeaRowMapper(), projectid,page.getOffSet(),page.getPageSize());
		
		page.setResult(list);
		return page;
	}
	
	public boolean save(Idea idea) {
		String sql = "insert into t_idea(id,title,content,createtime,employeeid,projectid) values(?,?,?,?,?,?)";
		return db.executeSQL(sql, idea.getId(),idea.getTitle(),idea.getContent(),idea.getCreatetime(),idea.getEmployeeid(),idea.getProjectid());
	}
	
	public Idea findByIdeaId(String id) {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT employeeid,projectid,te.username,te.password,te.pic,te.email,");
		sql.append("tp.name,tp.desc,ti.id,ti.title,ti.content,ti.createtime ");
		sql.append("FROM t_employee AS te LEFT JOIN t_idea AS ti ");
		sql.append("ON ti.employeeid = te.id LEFT JOIN t_project AS tp ");
		sql.append("ON ti.projectid = tp.id ");
		sql.append("WHERE ti.id = ?");
		return db.executeQueryForObject(sql.toString(), new IdeaRowMapper(),id);
	}
	public Long getCount(String projectid){
		String sql = "select count(*) from t_idea where projectid = ?";
		return (Long) db.executeQueryForFunctions(sql, projectid);
	}
	public boolean delete(String ideaid) {
		String sql = "delete from t_idea where id = ?";
		return db.executeSQL(sql, ideaid);
	}
	
	
	private class IdeaRowMapper implements RowMapper<Idea>{

		public Idea mapRow(ResultSet rs) throws SQLException {
			Idea idea = new Idea();
			idea.setContent(rs.getString("content"));
			idea.setCreatetime(rs.getString("createtime"));
			Employee e = new Employee();
			e.setEmail(rs.getString("email"));
			e.setId(rs.getString("employeeid"));
			e.setPassword(rs.getString("password"));
			e.setPic(rs.getString("pic"));
			e.setUsername(rs.getString("username"));
			idea.setEmployee(e);
			Project p = new Project();
			p.setCreatetime(rs.getString("createtime"));
			p.setDesc(rs.getString("desc"));
			p.setId(rs.getString("projectid"));
			p.setName(rs.getString("name"));
			idea.setProject(p);
			idea.setId(rs.getString("id"));
			idea.setTitle(rs.getString("title"));
			idea.setEmployeeid(e.getId());
			idea.setProjectid(p.getId());
			return idea;
		}
		
	}


	public boolean editIdea(String title, String content, String id) {
		String sql = "update t_idea set title = ?,content = ? where id = ?";
		return db.executeSQL(sql, title,content,id);
	}



	



	






	
}
